--------------------------------------------------------------------------------
-- @name: sort
-- @author: dion cho
-- @note: test case for direct mode inserting
--------------------------------------------------------------------------------

@capture_on
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on
set serveroutput off

drop table t_log_no_idx purge;
drop table t_log_with_idx purge;
drop table t_nolog_no_idx purge;
drop table t_nolog_with_idx purge;


create table t_log_no_idx (c1 char(100)) logging;
create table t_log_with_idx (c1 char(100)) logging;
create table t_nolog_no_idx (c1 char(100)) nologging;
create table t_nolog_with_idx (c1 char(100)) nologging;

create index t_log_with_idx_n1 on t_log_with_idx(c1);
create index t_nolog_with_idx_n1 on t_nolog_with_idx(c1);

----------------------------------------------
-- case 1: noarchive log mode + no_index
select name, log_mode from v$database;

truncate table t_log_no_idx;
truncate table t_log_with_idx;
truncate table t_nolog_no_idx;
truncate table t_nolog_with_idx;

exec tpack.begin_session_snapshot;

insert into t_log_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_log_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_nolog_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

col type format a6 
col item format a30 
col deltas format a30
select type, item, deltas 
from table(tpack.session_snapshot_report)
where type = 'STAT' and item in ('redo size', 'redo entries')
;


----------------------------------------------
-- case 2: noarchive log mode + with_index
select name, log_mode from v$database;

truncate table t_log_no_idx;
truncate table t_log_with_idx;
truncate table t_nolog_no_idx;
truncate table t_nolog_with_idx;

exec tpack.begin_session_snapshot;

insert into t_log_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_log_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_nolog_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

commit;

col type format a6 
col item format a30 
col deltas format a30
select type, item, deltas 
from table(tpack.session_snapshot_report)
where type = 'STAT' and item in ('redo size', 'redo entries')
;


-----------------------------------------------
-- alter database to archive log mode
connect sys/oracle@ukja1106 as sysdba
shutdown immediate
startup mount
alter database archivelog;
alter database open;

conn tpack/tpack@ukja1106

----------------------------------------------
-- case 3: archive log mode + no_index
select name, log_mode from v$database;

truncate table t_log_no_idx;
truncate table t_log_with_idx;
truncate table t_nolog_no_idx;
truncate table t_nolog_with_idx;

exec tpack.begin_session_snapshot;

insert into t_log_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_log_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_nolog_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

col type format a6 
col item format a30 
col deltas format a30
select type, item, deltas 
from table(tpack.session_snapshot_report)
where type = 'STAT' and item in ('redo size', 'redo entries')
;


----------------------------------------------
-- case 4: archive log mode + with_index
select name, log_mode from v$database;

truncate table t_log_no_idx;
truncate table t_log_with_idx;
truncate table t_nolog_no_idx;
truncate table t_nolog_with_idx;

exec tpack.begin_session_snapshot;

insert into t_log_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_log_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_nolog_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

commit;

col type format a6 
col item format a30 
col deltas format a30
select type, item, deltas 
from table(tpack.session_snapshot_report)
where type = 'STAT' and item in ('redo size', 'redo entries')
;



-- alter database to noarchive log mode
connect sys/oracle@ukja1106 as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

conn tpack/tpack@ukja1106

@capture_off
